package com.crm.util.xlsRead;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

	private String inputFile;

	public void setInputFile(String inputFile) {
		this.inputFile = inputFile;
	}

	public String read() throws IOException  {
		File inputWorkbook = new File(inputFile);
		StringBuilder sb=new StringBuilder();
		Workbook w;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines
			sb.append("<tr>"+
            "<td width='30' height='30' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'  style='background-repeat:repeat-x'></td>"+
            "<td width='30' height='30' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'  style='background-repeat:repeat-x'>S.No</td>"+
            "<td width='90' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'   style='background-repeat:repeat-x;bgcolor='#FFFFFF'> Part No </td>"+
            "<td width='135' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'   style='background-repeat:repeat-x;bgcolor='#FFFFFF'>Description</td>"+
            "<td width='135' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'   style='background-repeat:repeat-x;bgcolor='#FFFFFF'>Make</td>"+
            "<td width='51' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'   style='background-repeat:repeat-x;bgcolor='#FFFFFF'> Quantity </td>"+
            "<td width='90' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'   style='background-repeat:repeat-x;bgcolor='#FFFFFF'>Application</td>"+
            "<td width='144' align='left' valign='middle' background='<%=application.getContextPath() %>/images/table-box.jpg'   style='background-repeat:repeat-x;bgcolor='#FFFFFF'>Specification</td>"+
          "</tr>");
			for (int j =1; j < sheet.getRows(); j++) {
				System.out.println("Row Number "
						+  j);
				sb.append("<tr>");
				sb.append("<td valign='middle' bgcolor='#FFFFFF' align='center'>"+
				"<input type='checkbox'name='chkbox'>"+
				"</td>");
				for (int i =0; i < sheet.getColumns(); i++) {
					Cell cell = sheet.getCell(i,j);
					
					if(i==0){
						sb.append("<td bgcolor='#FFFFFF'><input name='sno"+(j)+"' type='text' value='"+(j)+"' size='5' /></td>");
					}
					if(i==1){
						sb.append("<td valign='top' bgcolor='#FFFFFF' align='left'><input id='partno"+(j)+"' class='part' type='text' size='5' value='"+cell.getContents()+"' name='partno"+(j)+"'></td>");
					}
					if(i==2){
						sb.append("<td valign='top' bgcolor='#FFFFFF' align='left'><input id='desc"+(j)+"' type='text' size='5' autocomplete='off' value='"+cell.getContents()+"' name='desc"+(j)+"'></td>");
					}
					if(i==3){
						sb.append("<td valign='top' bgcolor='#FFFFFF' align='left'><input id='make"+(j)+"' type='text' size='5' value='"+cell.getContents()+"' name='make"+(j)+"' autocomplete='off'></td>");
					}
					if(i==4){
						sb.append("<td valign='top' bgcolor='#FFFFFF' align='left'><input type='text' size='5' value='"+cell.getContents()+"' name='qty"+(j)+"'></td>");					}
					if(i==5){
						sb.append("<td valign='top' bgcolor='#FFFFFF' align='left'><input id='application"+(j)+"' type='text' autocomplete='off' size='5' value='"+cell.getContents()+"' name='application"+(j)+"'></td>");
					}
					if(i==6){
						sb.append("<td valign='top' bgcolor='#FFFFFF' align='left'><input id='specification"+(j)+"' type='text' size='5' value='"+cell.getContents()+"' name='specification"+(j)+"'></td>");
					}
				}
				sb.append("</tr>");
			}
			sb.append("<input type='hidden' id='rowcount' name='rowcount' value='"+sheet.getRows()+"'/>");
			System.out.println(sb);
		} catch (BiffException e) {
			e.printStackTrace();
		}
		return sb.toString();
	}

	public static void main(String[] args) throws IOException {
		ReadExcel test = new ReadExcel();
		test.setInputFile("C:/Documents and Settings/Sainath/Desktop/sample.xls");
		test.read();
	}

} 